﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input;
using Microsoft.Windows.Controls.Ribbon;
using StudentManagement.Entities;

namespace StudentManagement
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class MainWindow
    {
        readonly List<Student> _lstStudent = new List<Student>();
        readonly List<Subject> _lstSubject = new List<Subject>();
        readonly List<Marks> _lstMark = new List<Marks>();

        private const string MARK_CMD =
            "SELECT Student_Subject_ExamTable.Student_Code, StudentTable.Student_Name, Student_Subject_ExamTable.Subject_Code, Student_Subject_ExamTable.Exam_Seat_Time, Student_Subject_ExamTable.Mark, Student_Subject_ExamTable.Com_Exam"
            + " FROM StudentTable INNER JOIN Student_Subject_ExamTable ON StudentTable.[Student_Code] = Student_Subject_ExamTable.[Student_Code];";

        public MainWindow()
        {
            InitializeComponent();
            //PopulateSearchType();
            dgMain.Visibility = Visibility.Visible;
            dgSubject.Visibility = Visibility.Hidden;
            dgMarks.Visibility = Visibility.Hidden;
            btnEdit.IsEnabled = false;
            btnDelete.IsEnabled = false;
            btnEditSubject.IsEnabled = false;
            btnDeleteSubject.IsEnabled = false;
        }

        private void RibbonWindow_Loaded(object sender, RoutedEventArgs e)
        {
            LoadStudents("Select * FROM StudentTable");
        }

        #region Students
        private void LoadStudents(string strCommand)
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                var cmd = new OleDbCommand(strCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    // Clear old values
                    _lstStudent.Clear();

                    while (reader.Read())
                    {
                        var std = new Student
                        {
                            Student_Code = reader.GetString(0),
                            Student_Name = reader.GetString(1),
                            Class_Code = reader.GetString(2),
                            Student_DOB = reader.GetDateTime(3),
                            Student_Sex = reader.GetBoolean(4),
                            Current_Address = reader.GetString(5),
                            Contact_Address = reader.GetString(6)
                        };

                        _lstStudent.Add(std);
                    }
                }
                dgMain.ItemsSource = null;
                dgMain.ItemsSource = _lstStudent;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }

        private void AddStudent_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                var frmStudent = new FrmStudent
                {
                    Owner = this,
                    WindowStartupLocation = WindowStartupLocation.CenterOwner,
                    ShowInTaskbar = false
                };
                frmStudent.ShowDialog();
                LoadStudents("Select * FROM StudentTable");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void EditStudent_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                var selectedItem = (Student)dgMain.SelectedItem;

                var frmStudent = new FrmStudent(selectedItem)
                {
                    Owner = this,
                    WindowStartupLocation = WindowStartupLocation.CenterOwner,
                    ShowInTaskbar = false
                };
                frmStudent.ShowDialog();
                LoadStudents("Select * FROM StudentTable");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void DeleteStudent_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                DeleteStudent();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void SearchCommand_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                if (rbMain.SelectedTab.Label == "Students")
                {
                    if (string.IsNullOrEmpty(txtKeyword.Text.Trim()))
                    {
                        LoadStudents("Select * FROM StudentTable");
                        return;
                    }

                    var strCmd = string.Empty;

                    if ((bool) chkCode.IsChecked)
                    {
                        strCmd = "SELECT * FROM StudentTable WHERE Student_Code LIKE '%" + txtKeyword.Text.Trim() +
                                 "%';";
                    }
                    else
                    {
                        strCmd = "SELECT * FROM StudentTable WHERE Student_Name LIKE '%" + txtKeyword.Text.Trim() +
                                 "%';";
                    }

                    LoadStudents(strCmd);
                }

                if (rbMain.SelectedTab.Label == "Marks")
                {
                    if (string.IsNullOrEmpty(txtMarkKeyword.Text.Trim()))
                    {
                        LoadMarks(MARK_CMD);
                        return;
                    }

                    var strCmd = string.Empty;

                    if ((bool)chkCodeMark.IsChecked)
                    {
                        strCmd = "SELECT StudentTable.Student_Code, StudentTable.Student_Name, Student_Subject_ExamTable.Subject_Code, Student_Subject_ExamTable.Exam_Seat_Time, Student_Subject_ExamTable.Mark, Student_Subject_ExamTable.Com_Exam"
                                 + " FROM StudentTable INNER JOIN Student_Subject_ExamTable ON StudentTable.[Student_Code] = Student_Subject_ExamTable.[Student_Code]"
                                 + " WHERE StudentTable.Student_Code Like '%" + txtMarkKeyword.Text.Trim() + "%';";
                    }
                    else
                    {
                        strCmd = "SELECT StudentTable.Student_Code, StudentTable.Student_Name, Student_Subject_ExamTable.Subject_Code, Student_Subject_ExamTable.Exam_Seat_Time, Student_Subject_ExamTable.Mark, Student_Subject_ExamTable.Com_Exam"
                                 + " FROM StudentTable INNER JOIN Student_Subject_ExamTable ON StudentTable.[Student_Code] = Student_Subject_ExamTable.[Student_Code]"
                                 + " WHERE StudentTable.Student_Name Like '%" + txtMarkKeyword.Text.Trim() + "%';";
                    }

                    LoadMarks(strCmd);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void DeleteStudent()
        {
            OleDbConnection conn = null;
            try
            {
                var ret = MessageBox.Show("Are you sure want to delete selected Student(s)?", "Warning",
                                          MessageBoxButton.YesNoCancel, MessageBoxImage.Warning);
                if (ret != MessageBoxResult.Yes) return;

                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                foreach (var item in dgMain.SelectedItems)
                {
                    // Delete Subject
                    String strCommand = "DELETE FROM StudentTable WHERE Student_Code = '" +
                                        ((Student)item).Student_Code + "'";

                    var cmd = new OleDbCommand(strCommand, conn);
                    cmd.ExecuteNonQuery();

                    // Delete in Mark table
                    String strCmd = "DELETE FROM Student_Subject_ExamTable WHERE Student_Code = '" +
                                    ((Student)item).Student_Code + "'";

                    var cmdMark = new OleDbCommand(strCmd, conn);
                    cmdMark.ExecuteNonQuery();
                }
                MessageBox.Show("Delete Student(s) successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);

                // Refresh
                LoadStudents("Select * FROM StudentTable");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }
        #endregion

        #region Event handles
        private void ApplicationMain_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            // do nothing...
        }

        private void ApplicationExit(object sender, ExecutedRoutedEventArgs e)
        {
            Close();
        }

        private void dgMain_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                if (rbMain.SelectedTab.Label == "Students")
                {
                    if (dgMain.SelectedItems.Count > 0)
                    {
                        btnEdit.IsEnabled = dgMain.SelectedItems.Count == 1;
                        btnDelete.IsEnabled = true;
                    }
                    else
                    {
                        btnEdit.IsEnabled = false;
                        btnDelete.IsEnabled = false;
                    }
                }

                if (rbMain.SelectedTab.Label == "Subjects")
                {
                    if (dgSubject.SelectedItems.Count > 0)
                    {
                        btnEditSubject.IsEnabled = dgSubject.SelectedItems.Count == 1;
                        btnDeleteSubject.IsEnabled = true;
                    }
                    else
                    {
                        btnEditSubject.IsEnabled = false;
                        btnDeleteSubject.IsEnabled = false;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void dgMain_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {
            try
            {
                switch (rbMain.SelectedTab.Label)
                {
                    case "Students":
                        if (dgMain.SelectedItems.Count == 1)
                        {
                            var objStudent = (Student)dgMain.SelectedItem;
                            if (objStudent == null) return;

                            var frmStudent = new FrmStudent(objStudent);
                            frmStudent.Owner = this;
                            frmStudent.WindowStartupLocation = WindowStartupLocation.CenterOwner;
                            frmStudent.ShowInTaskbar = false;
                            frmStudent.ShowDialog();
                            LoadStudents("Select * FROM StudentTable");
                        }
                        break;
                    case "Subjects":
                        if (dgSubject.SelectedItems.Count == 1)
                        {
                            var objSub = (Subject)dgSubject.SelectedItem;
                            if (objSub == null) return;

                            var frmSub = new FrmSubject(objSub);
                            frmSub.Owner = this;
                            frmSub.WindowStartupLocation = WindowStartupLocation.CenterOwner;
                            frmSub.ShowInTaskbar = false;
                            frmSub.ShowDialog();
                            LoadSubjects();
                        }
                        break;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void dgMain_KeyUp(object sender, KeyEventArgs e)
        {
            try
            {
                if (e.Key == Key.Delete)
                {
                    switch (rbMain.SelectedTab.Label)
                    {
                        case "Students":
                            DeleteStudent();
                            break;
                        case "Subjects":
                            DeleteSubject();
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void Ribbon_SelectedTabChanged(object sender, DependencyPropertyChangedEventArgs e)
        {
            try
            {
                var ribbon = (Ribbon)sender;
                if (ribbon == null) return;

                switch (ribbon.SelectedTab.Label)
                {
                    case "Students":
                        dgMain.Visibility = Visibility.Visible;
                        dgSubject.Visibility = Visibility.Hidden;
                        dgMarks.Visibility = Visibility.Hidden;
                        LoadStudents("Select * FROM StudentTable");
                        break;
                    case "Subjects":
                        dgMain.Visibility = Visibility.Hidden;
                        dgSubject.Visibility = Visibility.Visible;
                        dgMarks.Visibility = Visibility.Hidden;
                        LoadSubjects();
                        break;
                    case "Marks":
                        dgMain.Visibility = Visibility.Hidden;
                        dgSubject.Visibility = Visibility.Hidden;
                        dgMarks.Visibility = Visibility.Visible;
                        LoadMarks(MARK_CMD);
                        break;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
        #endregion

        #region Subjects
        private void LoadSubjects()
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                var cmd = new OleDbCommand("Select * FROM SubjectTable", conn);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    // Clear old values
                    _lstSubject.Clear();

                    while (reader.Read())
                    {
                        var std = new Subject();

                        std.Subject_Code = reader.GetString(0);
                        std.Subject_Name = reader.GetString(1);
                        std.Term = reader.GetString(2);
                        std.Subject_Abbreviation = reader.GetString(3);
                        std.No_Of_Credit = Convert.ToInt32(reader.GetValue(4));
                        
                        _lstSubject.Add(std);
                    }
                }
                dgSubject.ItemsSource = null;
                dgSubject.ItemsSource = _lstSubject;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }

        private void AddSubject_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                var frmSubject = new FrmSubject
                                     {
                                         Owner = this,
                                         WindowStartupLocation = WindowStartupLocation.CenterOwner,
                                         ShowInTaskbar = false
                                     };
                frmSubject.ShowDialog();
                LoadSubjects();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void EditSubject_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                var selectedItem = (Subject)dgSubject.SelectedItem;

                var frmSubject = new FrmSubject(selectedItem)
                {
                    Owner = this,
                    WindowStartupLocation = WindowStartupLocation.CenterOwner,
                    ShowInTaskbar = false
                };
                frmSubject.ShowDialog();
                LoadSubjects();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private void DeleteSubject_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            DeleteSubject();
        }

        private void DeleteSubject()
        {
            OleDbConnection conn = null;
            try
            {
                var ret = MessageBox.Show("Are you sure want to delete selected Subject(s)?", "Warning",
                                          MessageBoxButton.YesNoCancel, MessageBoxImage.Warning);
                if (ret != MessageBoxResult.Yes) return;

                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                foreach (var item in dgSubject.SelectedItems)
                {
                    // Delete Subject
                    String strCommand = "DELETE FROM SubjectTable WHERE Subject_Code = '" +
                                        ((Subject)item).Subject_Code + "'";

                    var cmd = new OleDbCommand(strCommand, conn);
                    cmd.ExecuteNonQuery();

                    // Delete in Mark table
                    String strCmd = "DELETE FROM Student_Subject_ExamTable WHERE Subject_Code = '" +
                                    ((Subject)item).Subject_Code + "'";

                    var cmdMark = new OleDbCommand(strCmd, conn);
                    cmdMark.ExecuteNonQuery();
                }
                MessageBox.Show("Delete Subject(s) successfull.", "Infomation", MessageBoxButton.OK,
                                MessageBoxImage.Information);
                LoadSubjects();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (conn != null) conn.Close();
            }
        }

        #endregion

        #region Marks
        private void LoadMarks(string strCmd)
        {
            OleDbConnection conn = null;
            OleDbDataReader reader = null;

            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=StudentDB.mdb");
                conn.Open();

                var cmd = new OleDbCommand(strCmd, conn);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    // Clear old values
                    _lstMark.Clear();

                    while (reader.Read())
                    {
                        var mark = new Marks();

                        mark.Student_Code = reader.GetString(0);
                        mark.Student_Name = reader.GetString(1);
                        mark.Subject_Code = reader.GetString(2);
                        mark.Exam_Seat_Time = Convert.ToInt32(reader.GetValue(3));
                        mark.Mark = Convert.ToInt32(reader.GetValue(4));
                        mark.Com_Exam = reader.GetString(5);

                        _lstMark.Add(mark);
                    }
                }
                dgMarks.ItemsSource = null;
                dgMarks.ItemsSource = _lstMark;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn != null) conn.Close();
            }
        }
        #endregion

        private void chkCode_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            if(((RibbonCommand)e.Command).LabelTitle == "Student Code")
            {
                chkCode.IsChecked = true;
                chkName.IsChecked = false;
            }
            else
            {
                chkCode.IsChecked = false;
                chkName.IsChecked = true;
            }
        }

        private void RibbonCommand_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            if (((RibbonCommand)e.Command).LabelTitle == "Student Code")
            {
                chkCodeMark.IsChecked = true;
                chkNameMark.IsChecked = false;
            }
            else
            {
                chkCodeMark.IsChecked = false;
                chkNameMark.IsChecked = true;
            }
        }

        private void Report_Executed(object sender, ExecutedRoutedEventArgs e)
        {
            try
            {
                Window frmReport = null;

                switch (((RibbonCommand)e.Command).LabelTitle)
                {
                    case "Student Report":
                        frmReport = new FrmReport("Student");
                        break;
                    case "Subject Report":
                        frmReport = new FrmReport("Subject");
                        break;
                    case "Exam Seating Time Report":
                        frmReport = new FrmReport("SeattingTime");
                        break;
                }

                if (frmReport == null) return;

                frmReport.Owner = this;
                frmReport.WindowStartupLocation = WindowStartupLocation.CenterOwner;
                frmReport.ShowInTaskbar = false;

                frmReport.ShowDialog();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }
    }
}
